import os
import uuid
from flask import Blueprint, render_template, request, redirect, url_for, flash, send_from_directory
from database import get_db
from werkzeug.utils import secure_filename

# 创建因子管理Blueprint
factor_bp = Blueprint('factor', __name__, url_prefix='/factors')

# 配置上传文件夹
UPLOAD_FOLDER = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'uploads')
ALLOWED_EXTENSIONS = {'pdf', 'doc', 'docx', 'xls', 'xlsx', 'jpg', 'jpeg', 'png'}

# 确保上传文件夹存在
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

def allowed_file(filename):
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

@factor_bp.route('/')
def factor_list():
    search_query = request.args.get('search', '').strip()
    db = get_db()
    try:
        if search_query:
            # 修复搜索查询中的GROUP_CONCAT语法并添加分类路径
            factors = db.execute(
                'SELECT f.*, fc.name as category_name, '
                'GROUP_CONCAT(DISTINCT c.id || \':\' || c.name) AS coefficients, '
                'CONCAT_WS(\'->\', '
                '  (SELECT name FROM factor_categories WHERE id = fc.parent_id), '
                '  fc.name) as category_path '  # 添加分类路径生成
                'FROM carbon_factors f '
                'LEFT JOIN factor_categories fc ON f.category_id = fc.id '
                'LEFT JOIN factor_coefficients fcj ON f.id = fcj.factor_id '
                'LEFT JOIN carbon_coefficients c ON fcj.coefficient_id = c.id '
                'WHERE f.name LIKE ? OR fc.name LIKE ? '
                'GROUP BY f.id '
                'ORDER BY f.created_at DESC',
                ('%' + search_query + '%', '%' + search_query + '%')
            ).fetchall()
        else:
            # 修复默认查询中的GROUP_CONCAT语法并添加分类路径
            factors = db.execute(
                'SELECT f.*, fc.name as category_name, '
                'GROUP_CONCAT(DISTINCT c.id || \':\' || c.name) AS coefficients, '
                'CONCAT_WS(\'->\', '
                '  (SELECT name FROM factor_categories WHERE id = fc.parent_id), '
                '  fc.name) as category_path '  # 添加分类路径生成
                'FROM carbon_factors f '
                'LEFT JOIN factor_categories fc ON f.category_id = fc.id '
                'LEFT JOIN factor_coefficients fcj ON f.id = fcj.factor_id '
                'LEFT JOIN carbon_coefficients c ON fcj.coefficient_id = c.id '
                'GROUP BY f.id '
                'ORDER BY f.created_at DESC'
            ).fetchall()
    except sqlite3.Error as e:
        # 处理数据库错误
        flash(f'数据库错误: {str(e)}', 'danger')
        factors = []
    finally:
        # 确保数据库连接关闭
        db.close()
    return render_template('factors.html', factors=factors, search_query=search_query)

@factor_bp.route('/add', methods=['GET', 'POST'])
def add_factor():
    db = get_db()
    
    if request.method == 'POST':
        name = request.form['name']
        gas_type = request.form['gas_type']
        value_type = request.form['value_type']
        emission_value = request.form['emission_value']
        source = request.form['source']
        factor_type = request.form['factor_type']
        category_id = request.form['category_id']
        formula_description = request.form['formula_description']
        
        # 验证必填字段
        if not name or not gas_type or not value_type or not emission_value:
            flash('请填写所有必填字段', 'danger')
            return redirect(url_for('factor.add_factor'))
        
        # 插入因子基本信息
        cursor = db.execute(
            'INSERT INTO carbon_factors (name, gas_type, value_type, emission_value, source, factor_type, category_id) VALUES (?, ?, ?, ?, ?, ?, ?)',
            (name, gas_type, value_type, emission_value, source, factor_type, category_id)
        )
        factor_id = cursor.lastrowid
        
        # 插入公式说明
        if formula_description:
            db.execute(
                'INSERT INTO factors_formulas (factor_id, formula_description) VALUES (?, ?)',
                (factor_id, formula_description)
            )
        
        db.commit()
        flash('碳排放因子添加成功', 'success')
        return redirect(url_for('factor.edit_factor', id=factor_id))
    
    # 获取层级分类（使用与编辑界面相同的递归CTE查询）
    categories = db.execute('''
        WITH RECURSIVE category_hierarchy AS (
            SELECT id, name, parent_id, level, 
                   CAST(printf('%03d', id) AS TEXT) AS hierarchy_path
            FROM factor_categories
            WHERE id = 1
            UNION ALL
            SELECT c.id, c.name, c.parent_id, c.level, 
                   ch.hierarchy_path || '.' || printf('%03d', c.id) AS hierarchy_path
            FROM factor_categories c
            JOIN category_hierarchy ch ON c.parent_id = ch.id
            ORDER BY c.id
        )
        SELECT id, name, level
        FROM category_hierarchy
        ORDER BY hierarchy_path
    ''').fetchall()
    
    return render_template('add_factor.html', categories=categories)

@factor_bp.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit_factor(id):
    db = get_db()
    factor = db.execute('SELECT * FROM carbon_factors WHERE id = ?', (id,)).fetchone()
    if factor is None:
        flash('碳排放因子不存在', 'danger')
        return redirect(url_for('factor.factor_list'))
    
    # 获取层级分类（使用表中已存在的level字段）
    categories = db.execute('''
        WITH RECURSIVE category_hierarchy AS (
            SELECT id, name, parent_id, level, 
                   CAST(printf('%03d', id) AS TEXT) AS hierarchy_path  -- 根分类ID格式化为3位字符串
            FROM factor_categories
            WHERE id = 1
            UNION ALL
            SELECT c.id, c.name, c.parent_id, c.level, 
                   ch.hierarchy_path || '.' || printf('%03d', c.id) AS hierarchy_path
            FROM factor_categories c
            JOIN category_hierarchy ch ON c.parent_id = ch.id
            ORDER BY c.id  -- 子分类按ID升序排列
        )
        SELECT id, name, parent_id, level
        FROM category_hierarchy
        ORDER BY hierarchy_path
    ''').fetchall()
    print("Categories:", categories)  # 添加日志输出
    
    # 获取公式信息
    formula = db.execute('SELECT * FROM factors_formulas WHERE factor_id = ?', (id,)).fetchone()
    
    # 获取已关联的系数
    associated_coefficients = db.execute(
        'SELECT c.id, c.name, c.gas, c.value, c.unit, c.source '
        'FROM factor_coefficients fcj '
        'JOIN carbon_coefficients c ON fcj.coefficient_id = c.id '
        'WHERE fcj.factor_id = ?',
        (id,)
    ).fetchall()
    
    # 获取证明材料
    documents = db.execute(
        'SELECT * FROM factor_documents WHERE factor_id = ? ORDER BY uploaded_at DESC', (id,)
    ).fetchall()
    
    if request.method == 'POST':
        # 更新基本信息
        name = request.form['name']
        gas_type = request.form['gas_type']
        value_type = request.form['value_type']
        emission_value = request.form['emission_value']
        source = request.form['source']
        factor_type = request.form['factor_type']
        category_id = request.form['category_id']
        formula_description = request.form['formula_description']
        
        db.execute(
            'UPDATE carbon_factors SET name = ?, gas_type = ?, value_type = ?, emission_value = ?, source = ?, factor_type = ?, category_id = ? WHERE id = ?',
            (name, gas_type, value_type, emission_value, source, factor_type, category_id, id)
        )
        
        # 更新公式说明
        if formula:
            db.execute(
                'UPDATE factors_formulas SET formula_description = ? WHERE factor_id = ?',
                (formula_description, id)
            )
        elif formula_description:
            db.execute(
                'INSERT INTO factors_formulas (factor_id, formula_description) VALUES (?, ?)',
                (id, formula_description)
            )
        
        db.commit()
        flash('碳排放因子更新成功', 'success')
        return redirect(url_for('factor.edit_factor', id=id))
    
    return render_template('edit_factor.html', 
                          factor=factor, 
                          formula=formula, 
                          categories=categories, 
                          associated_coefficients=associated_coefficients, 
                          documents=documents)

@factor_bp.route('/<int:id>/delete', methods=['POST'])
def delete_factor(id):
    db = get_db()
    
    # 删除因子（级联删除相关表数据）
    db.execute('DELETE FROM carbon_factors WHERE id = ?', (id,))
    db.commit()
    flash('碳排放因子删除成功', 'success')
    return redirect(url_for('factor.factor_list'))

@factor_bp.route('/associate_coefficient/<int:factor_id>', methods=['POST'])
def associate_coefficient(factor_id):
    db = get_db()
    coefficient_id = request.form['coefficient_id']
    
    # 检查关联是否已存在
    exists = db.execute(
        'SELECT id FROM factor_coefficients WHERE factor_id = ? AND coefficient_id = ?',
        (factor_id, coefficient_id)
    ).fetchone()
    
    if exists:
        flash('该系数已关联', 'warning')
    else:
        db.execute(
            'INSERT INTO factor_coefficients (factor_id, coefficient_id) VALUES (?, ?)',
            (factor_id, coefficient_id)
        )
        db.commit()
        flash('系数关联成功', 'success')
    
    return redirect(url_for('factor.edit_factor', id=factor_id))

@factor_bp.route('/remove_coefficient/<int:factor_id>/<int:coefficient_id>', methods=['POST'])
def remove_coefficient(factor_id, coefficient_id):
    db = get_db()
    
    db.execute(
        'DELETE FROM factor_coefficients WHERE factor_id = ? AND coefficient_id = ?',
        (factor_id, coefficient_id)
    )
    db.commit()
    flash('系数关联已移除', 'success')
    
    return redirect(url_for('factor.edit_factor', id=factor_id))

@factor_bp.route('/upload_document/<int:factor_id>', methods=['POST'])
def upload_document(factor_id):
    if 'document' not in request.files:
        flash('未选择文件', 'danger')
        return redirect(url_for('factor.edit_factor', id=factor_id))
    
    file = request.files['document']
    
    if file.filename == '':
        flash('未选择文件', 'danger')
        return redirect(url_for('factor.edit_factor', id=factor_id))
    
    if file and allowed_file(file.filename):
        # 生成唯一文件名
        filename = secure_filename(file.filename)
        unique_filename = f"{uuid.uuid4().hex}_{filename}"
        file_path = os.path.join(UPLOAD_FOLDER, unique_filename)
        
        # 保存文件
        file.save(file_path)
        
        # 记录到数据库
        db = get_db()
        db.execute(
            'INSERT INTO factor_documents (factor_id, file_name, file_path) VALUES (?, ?, ?)',
            (factor_id, filename, unique_filename)
        )
        db.commit()
        
        flash('文件上传成功', 'success')
    else:
        flash('不支持的文件类型', 'danger')
    
    return redirect(url_for('factor.edit_factor', id=factor_id))

@factor_bp.route('/remove_document/<int:doc_id>', methods=['POST'])
def remove_document(doc_id):
    db = get_db()
    document = db.execute('SELECT * FROM factor_documents WHERE id = ?', (doc_id,)).fetchone()
    
    if document:
        # 删除文件
        file_path = os.path.join(UPLOAD_FOLDER, document['file_path'])
        if os.path.exists(file_path):
            os.remove(file_path)
        
        # 删除数据库记录
        db.execute('DELETE FROM factor_documents WHERE id = ?', (doc_id,))
        db.commit()
        flash('文件已删除', 'success')
    else:
        flash('文件不存在', 'danger')
    
    return redirect(url_for('factor.edit_factor', id=document['factor_id']))

@factor_bp.route('/documents/<filename>')
def view_document(filename):
    return send_from_directory(UPLOAD_FOLDER, filename)

@factor_bp.route('/search_coefficients/<int:factor_id>', methods=['GET'])
def search_coefficients(factor_id):
    db = get_db()  # 添加数据库连接初始化
    query = request.args.get('query', '')
    coefficients = db.execute(
        'SELECT * FROM carbon_coefficients WHERE name LIKE ?',
        ('%' + query + '%',)
    ).fetchall()
    # 添加返回语句以渲染搜索结果模板
    return render_template('coefficient_search_results.html', 
                          coefficients=coefficients, 
                          factor_id=factor_id)
